using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Xml;
using System.Reflection;

namespace CaseData{

    public enum CaseStatus {
        All = 0,
        New = 1,
        CustomerContacted = 2,
        PendingRepair = 3,
        PendingApproval = 4,
        PendingDelivery = 5,
        InProgress = 6,
        FollowUp = 7,
        Closed = 8,
        Inactive = 9
    }
    public enum WhereFrom {
        Assigned = 1,
        LiveLine = 2
    }
    public struct CaseStatusPair {
        private CaseStatus m_status;
        private string m_name;
        public CaseStatusPair ( CaseStatus caseStatus, string name ) {
            m_status = caseStatus;
            m_name = name;
        }

        public CaseStatus CaseStatus {
            get { return m_status; }
            set { m_status = value; }
        }

        public int CaseStatusInt {
            get { return (int)m_status; }
            set { m_status = (CaseStatus) value; }
        }

        public string Name {
            get { return m_name; }
            set { m_name = value; }
        }
    }
    public struct WhereFromPair {
        private WhereFrom m_whereFrom;
        private string m_name;
        public WhereFromPair ( WhereFrom whereFrom, string name ) {
            m_whereFrom = whereFrom;
            m_name = name;
        }
        public WhereFrom WhereFrom {
            get { return m_whereFrom; }
            set { m_whereFrom = value; }
        }

        public int WhereFromInt {
            get { return (int) m_whereFrom; }
            set { m_whereFrom = (WhereFrom) value; }
        }
        public string Name {
            get { return m_name; }
            set { m_name = value; }
        }
    }
    public struct FileInformation {
        private string m_Version;
        private DateTime m_CreatedDate;
        private DateTime m_ModifiedDate;
        public FileInformation ( string version, DateTime created, DateTime modified ) {
            m_Version = version;
            m_CreatedDate = created;
            m_ModifiedDate = modified;
        }
        public FileInformation ( CaseBase.FileRow fRow ) {
            m_Version = fRow.FileVersion;
            m_CreatedDate = fRow.CreatedDate;
            m_ModifiedDate = fRow.ModifiedDate;
        }
        public DateTime CreatedDateTime {
            get { return m_CreatedDate; }
            set { m_CreatedDate = value; }
        }
        public DateTime ModifiedDateTime {
            get { return m_ModifiedDate; }
            set { m_ModifiedDate = value; }
        }
        public string Version {
            get { return m_Version; }
            set { m_Version = value; }
        }
    }

    public class CaseDataAccess:CaseBase{
        public FileInformation FileInfo;
        private const string CurrentFileVersion = "0.2";

        private CaseDataView cdvAllCases;
        private CaseDataView cdvNewCases;
        private CaseDataView cdvCustomerContactedCases;
        private CaseDataView cdvPendingRepairCases;
        private CaseDataView cdvPendingApprovalCases;
        private CaseDataView cdvPendingDeliveryCases;
        private CaseDataView cdvInProgressCases;
        private CaseDataView cdvFollowUpCases;
        private CaseDataView cdvClosedCases;
        private CaseDataView cdvInActiveCases;
        private List<CaseStatusPair> caseStatusList;
        private List<WhereFromPair> whereFromList;


        public CaseDataAccess(string xmlFileName) {
            ReadCaseData(xmlFileName);
            InitDataViews();
            PopulateLists();
        }

        public CaseDataAccess () {
            PopulateLists();
        }


        private void PopulateLists () {
            caseStatusList = new List<CaseStatusPair>();
            caseStatusList.Add(new CaseStatusPair(CaseStatus.New, "New"));
            caseStatusList.Add(new CaseStatusPair(CaseStatus.CustomerContacted, "Customer contacted"));
            caseStatusList.Add(new CaseStatusPair(CaseStatus.PendingRepair, "Pending repair"));
            caseStatusList.Add(new CaseStatusPair(CaseStatus.PendingApproval, "Pending approval"));
            caseStatusList.Add(new CaseStatusPair(CaseStatus.PendingDelivery, "Pending delivery"));
            caseStatusList.Add(new CaseStatusPair(CaseStatus.InProgress, "In progress"));
            caseStatusList.Add(new CaseStatusPair(CaseStatus.FollowUp, "Follow up"));
            caseStatusList.Add(new CaseStatusPair(CaseStatus.Closed, "Closed"));

            whereFromList = new List<WhereFromPair>();
            whereFromList.Add(new WhereFromPair(WhereFrom.Assigned, "Assigned"));
            whereFromList.Add(new WhereFromPair(WhereFrom.LiveLine, "Live line"));
        }
        private void InitDataViews(){
            cdvAllCases = new CaseDataView(CaseStatus.All, this.CaseItem);
            cdvNewCases = new CaseDataView(CaseStatus.New, this.CaseItem);
            cdvCustomerContactedCases = new CaseDataView(CaseStatus.CustomerContacted, this.CaseItem);
            cdvPendingRepairCases = new CaseDataView(CaseStatus.PendingRepair, this.CaseItem);
            cdvPendingApprovalCases = new CaseDataView(CaseStatus.PendingApproval, this.CaseItem);
            cdvPendingDeliveryCases = new CaseDataView(CaseStatus.PendingDelivery, this.CaseItem);
            cdvInProgressCases = new CaseDataView(CaseStatus.InProgress, this.CaseItem);
            cdvFollowUpCases = new CaseDataView(CaseStatus.FollowUp, this.CaseItem);
            cdvClosedCases = new CaseDataView(CaseStatus.Closed, this.CaseItem);
            cdvInActiveCases = new CaseDataView(CaseStatus.Inactive, this.CaseItem);

            cdvAllCases.Sort = "RecievedTime DESC";
            cdvNewCases.Sort = "RecievedTime DESC";
            cdvCustomerContactedCases.Sort = "RecievedTime DESC";
            cdvPendingRepairCases.Sort = "RecievedTime DESC";
            cdvPendingApprovalCases.Sort = "RecievedTime DESC";
            cdvPendingDeliveryCases.Sort = "RecievedTime DESC";
            cdvInProgressCases.Sort = "RecievedTime DESC";
            cdvFollowUpCases.Sort = "RecievedTime DESC";
            cdvClosedCases.Sort = "RecievedTime DESC";
            cdvInActiveCases.Sort = "CloseDate DESC";

            //dvNewCases.RowFilter = "CaseStatusId = '" + (int) CaseStatus.New + "'";
            //dvCustomerContactedCases.RowFilter = "CaseStatusId = '" + (int) CaseStatus.CustomerContacted + "'";
            //dvPendingRepairCases.RowFilter = "CaseStatusId = '" + (int) CaseStatus.PendingRepair + "'";
            //dvPendingApprovalCases.RowFilter = "CaseStatusId = '" + (int) CaseStatus.PendingApproval + "'";
            //dvPendingDeliveryCases.RowFilter = "CaseStatusId = '" + (int) CaseStatus.PendingDelivery + "'";
            //dvInProgressCases.RowFilter = "CaseStatusId = '" + (int) CaseStatus.InProgress + "'";
            //dvFollowUpCases.RowFilter = "CaseStatusId = '" + (int) CaseStatus.FollowUp + "'";
            //dvClosedCases.RowFilter = "CaseStatusId = '" + (int) CaseStatus.Closed + "'";
        }

        #region Add Update Delete cases
        public void UpdateCaseRow ( CaseRow caseRow ) {
            //tblCaseRow cRow = m_CaseBase.tblCase.FindByCaseId(rowId);
            CaseItemRow cRow = this.CaseItem.FindByCaseId(caseRow.CaseId);
            cRow.CaseId = caseRow.CaseId;
            cRow.TicketId = caseRow.TicketId;
            cRow.ComplaintId = caseRow.ComplaintId;
            cRow.WhereFromId = (int) caseRow.WhereFromId;
            cRow.CaseStatusId = (int) caseRow.CaseStatusId;
            cRow.Description = caseRow.Description;
            cRow.RecievedTime = caseRow.RecievedDateTime;
            cRow.DayOneCallOne = caseRow.DayOneCallOne;
            cRow.DayOneCallTwo = caseRow.DayOneCallTwo;
            cRow.DayOneEmail = caseRow.DayOneEmail;
            cRow.DayTwoCallOne = caseRow.DayTwoCallOne;
            cRow.DayTwoCallTwo = caseRow.DayTwoCallTwo;
            cRow.DayThreeCallOne = caseRow.DayThreeCallOne;
            cRow.DayFourCallOne = caseRow.DayFourCallOne;
            cRow.DayFiveCallOne = caseRow.DayFiveCallOne;
            cRow.NoContactLetter = caseRow.NoContactLetter;
            cRow.DaySixCallOne = caseRow.DaySixCallOne;
            cRow.DaySevenCallOne = caseRow.DaySevenCallOne;
            cRow.CustomerName = caseRow.CustomerName;
            cRow.CustomerAdress = caseRow.CustomerAdress;
            //cRow.Notes = caseRow.Notes;
            //cRow.ToDo = caseRow.ToDo;
        }
        public void AddCaseRow ( CaseRow caseRow ) {
            this.CaseItem.AddCaseItemRow(
                caseRow.TicketId,
                caseRow.ComplaintId,
                (int) caseRow.WhereFromId,
                (int) caseRow.CaseStatusId,
                caseRow.Description,
                caseRow.RecievedDateTime,
                caseRow.DayOneCallOne,
                caseRow.DayOneEmail,
                caseRow.DayTwoCallTwo,
                caseRow.DayTwoCallOne,
                caseRow.DayTwoCallTwo,
                caseRow.DayThreeCallOne,
                caseRow.DayFourCallOne,
                caseRow.DayFiveCallOne,
                caseRow.NoContactLetter,
                caseRow.DaySixCallOne,
                caseRow.DaySevenCallOne,
                caseRow.CustomerName,
                caseRow.CustomerAdress,
                caseRow.CloseDate,
                caseRow.DayOneCallThree);
        }
        public void DeleteCaseRow ( CaseRow caseRow ) {
            CaseItemRow cRow = this.CaseItem.FindByCaseId(caseRow.CaseId);
            this.CaseItem.RemoveCaseItemRow(cRow);
        }
        public void DeleteCaseRow ( long caseId) {
            CaseItemRow cRow = this.CaseItem.FindByCaseId(caseId);
            this.CaseItem.RemoveCaseItemRow(cRow);
        }
        #endregion

        #region Read Write Xml
        public void WriteCaseData( string xmlFileName ) {
            //Set file info
            if( this.File.Rows.Count > 0 ) {
                FileRow fr = (FileRow) this.File.Rows[0];
                fr.FileVersion = CurrentFileVersion;
                fr.ModifiedDate = DateTime.Now;
            }
            else {
                this.File.AddFileRow(CurrentFileVersion, DateTime.Now,DateTime.Now);
            }
            this.WriteXml(xmlFileName);
        }
        
        public void ReadCaseData( string xmlFileName ) {
            XmlDocument doc = new XmlDocument();
            doc.Load(xmlFileName);
            XmlNode fileNode = doc.SelectSingleNode("/CaseBase/File");

            //Conversion from file version 0.1 (lacks version info)
            if( fileNode == null ) {
                doc.InnerXml=doc.InnerXml.Replace("tblCase", "CaseItem");
                XmlNodeList cases = doc.SelectNodes("CaseBase/CaseItem");
                foreach (XmlNode cs in cases){ //Convert the two customer name columns into one
                    XmlNode test = doc.CreateNode(XmlNodeType.Element, "CustomerName", null);
                    test.InnerText = cs.SelectSingleNode("CustomerFirstName").InnerText;
                    test.InnerText += " " + cs.SelectSingleNode("CustomerSecondName").InnerText;
                    test.InnerText = test.InnerText.Trim();
                    cs.AppendChild(test);

                    //Convert CallOne and CallTwo -> DaySixCallOne and DaySevenCallOne
                    XmlNode d6call1 = doc.CreateNode(XmlNodeType.Element,"DaySixCallOne",null);
                    XmlNode d7call1 = doc.CreateNode(XmlNodeType.Element,"DaySevenCallOne",null);
                    XmlNode d1call3 = doc.CreateNode(XmlNodeType.Element, "DayOneCallThree", null);
                    XmlNode closeDate = doc.CreateNode(XmlNodeType.Element, "CloseDate", null);
                    d6call1.InnerText = cs.SelectSingleNode("CallOne").InnerText;
                    d7call1.InnerText = cs.SelectSingleNode("CallTwo").InnerText;
                    d1call3.InnerText = "false";
                    closeDate.InnerText = DateTime.MinValue.ToShortDateString();
                    cs.AppendChild(d6call1);
                    cs.AppendChild(d7call1);
                    cs.AppendChild(d1call3);
                    cs.AppendChild(closeDate);
                }
                this.ReadXml(new XmlNodeReader(doc));
            }
            else { //other version handling in the future should be placed here
                if( this.File.Rows.Count > 0 ) {
                    FileInfo = new FileInformation((FileRow) this.File.Rows[0]);
                }
                this.ReadXml(xmlFileName);
            }
        }

        private void GenerateXmlSchema ( string xsdFileName ) {

            DataSet dataSet = new DataSet("CaseBase");

            // create Case table (used to create the first XmlSchema and is not up to date with current version)
            DataTable tblCase = dataSet.Tables.Add("tblCase");
            DataColumn caseId = tblCase.Columns.Add("CaseId", typeof(Int32));
            tblCase.Columns.Add("TicketId", typeof(Int32));
            tblCase.Columns.Add("ComplaintId", typeof(Int32));
            tblCase.Columns.Add("WhereFromId", typeof(Int32));
            tblCase.Columns.Add("CaseStatusId", typeof(Int32));
            tblCase.Columns.Add("Description", typeof(String));
            tblCase.Columns.Add("RecievedTime", typeof(DateTime));

            tblCase.Columns.Add("DayOneCallOne", typeof(Boolean));
            tblCase.Columns.Add("DayOneEmail", typeof(Boolean));
            tblCase.Columns.Add("DayOneCallTwo", typeof(Boolean));
            tblCase.Columns.Add("DayTwoCallOne", typeof(Boolean));
            tblCase.Columns.Add("DayTwoCallTwo", typeof(Boolean));
            tblCase.Columns.Add("DayThreeCallOne", typeof(Boolean));
            tblCase.Columns.Add("DayFourCallOne", typeof(Boolean));
            tblCase.Columns.Add("DayFiveCallOne", typeof(Boolean));
            tblCase.Columns.Add("NoContactLetter", typeof(String));
            tblCase.Columns.Add("CallOne", typeof(Boolean));
            tblCase.Columns.Add("CallTwo", typeof(Boolean));

            tblCase.Columns.Add("CustomerFirstName", typeof(String));
            tblCase.Columns.Add("CustomerSecondName", typeof(String));

            tblCase.Columns.Add("Notes", typeof(String));
            tblCase.Columns.Add("ToDo", typeof(String));
            tblCase.Columns.Add("CustomerAdress", typeof(String));

            tblCase.PrimaryKey = new DataColumn[] { caseId };

            // write dataset to file
            //dataSet.WriteXmlSchema("../../CaseBase.xsd");
            dataSet.WriteXmlSchema(xsdFileName);
        }
        #endregion

        #region DataView-properties, Filters And Search methods
        public CaseDataView AllCases {
            get { return cdvAllCases; }
        }
        public CaseDataView NewCases {
            get { return cdvNewCases; }
        }
        public CaseDataView CustomerContactedCases {
            get { return cdvCustomerContactedCases; }
        }
        public CaseDataView PendingDeliveryCases {
            get { return cdvPendingDeliveryCases; }
        }
        public CaseDataView PendingRepairCases {
            get { return cdvPendingRepairCases; }
        }
        public CaseDataView PendingApprovalCases {
            get { return cdvPendingApprovalCases; }
        }
        public CaseDataView InProgressCases {
            get { return cdvInProgressCases; }
        }
        public CaseDataView FollowUpCases {
            get { return cdvFollowUpCases; }
        }
        public CaseDataView ClosedCases {
            get { return cdvClosedCases; }
        }
        public CaseDataView InactiveCases {
            get { return cdvClosedCases; }
        }
        //public string FilterAllCases {
        //    set{dvAllCases.RowFilter = SearchFilter(value);}
        //}
        //public string FilterNewCases {
        //    set { dvNewCases.RowFilter = SearchFilter(CaseStatus.New, value);}
        //}
        //public string FilterCustomerContactedCases {
        //    set { dvCustomerContactedCases.RowFilter = SearchFilter(CaseStatus.CustomerContacted, value); }
        //}
        //public string FilterPendingDeliveryCases{
        //    set{dvPendingDeliveryCases.RowFilter = SearchFilter(CaseStatus.PendingDelivery, value);}
        //}
        //public string FilterPendingRepairCases {
        //    set { dvPendingRepairCases.RowFilter = SearchFilter(CaseStatus.PendingRepair, value); }
        //}
        //public string FilterPendingApprovalCases {
        //    set{dvPendingApprovalCases.RowFilter = SearchFilter(CaseStatus.PendingApproval, value);}
        //}
        //public string FilterInProgressCases {
        //    set{dvInProgressCases.RowFilter = SearchFilter(CaseStatus.InProgress, value);}
        //}
        //public string FilterFollowUpCases {
        //    set{dvFollowUpCases.RowFilter = SearchFilter(CaseStatus.FollowUp, value);}
        //}
        //public string FilterClosedCases {
        //    set{dvClosedCases.RowFilter = SearchFilter(CaseStatus.Closed, value);}
        //}

        //public void FilterCaseView ( CaseView cw, string filter ) {
        //    string m_filter = @"( CustomerName LIKE '%" + filter + "%' ";
        //    m_filter += " OR Description LIKE '%" + filter + "%' ";
        //    m_filter += " OR TicketId LIKE '%" + filter + "%' ";
        //    m_filter += " OR ComplaintId LIKE '%" + filter + "%' ";
        //    m_filter += ")";
        //    cw.RowFilter = m_filter;
        //}

        //private string SearchFilter (CaseStatus status, string filter) {
        //    string m_filter = "CaseStatusId = '" + (int) status + "' AND ";
        //    //m_filter += @"( CustomerFirstName LIKE '%" + filter + "%' OR CustomerSecondName LIKE '%" + filter + "%'";
        //    m_filter += @"( CustomerName LIKE '%" + filter + "%' ";
        //    m_filter += " OR Description LIKE '%" + filter + "%' ";
        //    m_filter += " OR TicketId LIKE '%" + filter + "%' ";
        //    m_filter += " OR ComplaintId LIKE '%" + filter + "%' ";
        //    m_filter += ")";
        //    return m_filter;
        //}
        //private string SearchFilter (string filter ) {
        //    //string m_filter = @"( CustomerFirstName LIKE '%" + filter + "%' OR CustomerSecondName LIKE '%" + filter + "%'";
        //    string m_filter = @"( CustomerName LIKE '%" + filter + "%' ";
        //    m_filter += " OR Description LIKE '%" + filter + "%' ";
        //    m_filter += " OR TicketId LIKE '%" + filter + "%' ";
        //    m_filter += " OR ComplaintId LIKE '%" + filter + "%' ";
        //    m_filter += ")";
        //    return m_filter;
        //}

        #endregion

        #region Excel Import
        private DataTable getDataFromXLS ( string strFilePath ) {
            try {
                string strConnectionString = string.Empty;
                strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
                OleDbConnection cnCSV = new OleDbConnection(strConnectionString);
                cnCSV.Open();
                OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [Active Cases$]", cnCSV);
                OleDbDataAdapter daCSV = new OleDbDataAdapter();
                daCSV.SelectCommand = cmdSelect;
                DataTable dtCSV = new DataTable();
                daCSV.Fill(dtCSV);
                cnCSV.Close();
                daCSV = null;
                return dtCSV;
            }
            catch( Exception ex ) {
                throw new Exception("Excel file could not be read: " + ex.Message);
            }
        }

        public void ReadImportedDataTable ( string fileName ) {
            DataTable dt = getDataFromXLS(fileName);

            foreach( DataColumn cd in dt.Columns ) {
                System.Console.WriteLine(cd.ColumnName);
            }
            int rowNumber = 1;
            foreach( DataRow dr in dt.Rows ) {
                rowNumber++;
                try {
                    CaseRow cr = new CaseRow();

                    cr.RecievedDateTime = getDateTimeFromString(dr["DateRecieved"].ToString(), dr["TimeReceived"].ToString());
                    cr.WhereFromId = getWhereFromFromString(dr["WhereFromId"].ToString());
                    cr.ComplaintId = dr["ComplaintId"].ToString();
                    cr.TicketId = dr["TicketId"].ToString();
                    cr.DayOneCallOne = checkForYesNo(dr["DOneCallOne"].ToString(), "Day one call one");
                    cr.DayOneEmail = checkForYesNo(dr["DOneE-Mail"].ToString(), "Day one E-Mail");
                    cr.DayOneCallTwo = checkForYesNo(dr["DOneCallTwo"].ToString(), "Day one call two");
                    cr.DayTwoCallOne = checkForYesNo(dr["DTwoCallOne"].ToString(), "Day two call one");
                    cr.DayTwoCallTwo = checkForYesNo(dr["DTwoCallTwo"].ToString(), "Day two call two");
                    cr.DayThreeCallOne = checkForYesNo(dr["DThreeCallOne"].ToString(), "Day three call one");
                    cr.DayFourCallOne = checkForYesNo(dr["DFourCallOne"].ToString(), "Day four call one");
                    cr.DayFiveCallOne = checkForYesNo(dr["DFiveCallOne"].ToString(), "Day five call one");
                    //cr.Notes = dr["F18"].ToString();
                    cr.CustomerName = dr["Name"].ToString();
                    if( dr["Phone"].ToString() != "" )
                        cr.CustomerAdress += "Phone: " + dr["Phone"].ToString() + "\n";
                    if( dr["EmailAddess"].ToString() != "" )
                        cr.CustomerAdress += "Email: " + dr["EmailAddess"].ToString();
                    cr.CaseStatusId = getStatusFromString(dr["StatusId"].ToString());
                    cr.Description = dr["Description"].ToString();
                    //Try to find customer name in description
                    if( cr.CustomerName.Length == 0 ) {
                        tryToGetName(cr, cr.Description);
                    }
                    //cr.ToDo = "";
                    this.AddCaseRow(cr);

                }
                catch( Exception e ) {
                    string msg = "Could not import case or row " + rowNumber + "  because:\n";
                    throw new Exception(msg + e.Message);
                }

            }
        }

        private bool checkForYesNo (string columnValue, string columnName) {
            string originalValue = columnValue;
            columnValue = columnValue.ToLower().Replace(" ", "");
            if( columnValue == "x" ) { return true; }
            else if( columnValue == "yes" ) { return true; }
            else if( columnValue == "*" ) { return true; }
            else if( columnValue == "no" ) { return false; }
            else if( columnValue == @"n/a" ) { return false; }
            else if( columnValue == "" ) { return false; }
            else {
                throw new Exception(columnName + " cannot be translated: " + originalValue);
            }
        }

        private CaseStatus getStatusFromString ( string columnValue ) {
            string originalValue = columnValue;
            columnValue = columnValue.ToLower().Replace(" ", "");
            if( columnValue == "followup" ) { return CaseStatus.FollowUp; }
            else if( columnValue == "closed" ) { return CaseStatus.Closed; }
            else if( columnValue == "pendingrepair" ) { return CaseStatus.PendingRepair; }
            else if( columnValue == "pendingdelivery" ) { return CaseStatus.PendingDelivery; }
            else if( columnValue == "new" ) { return CaseStatus.New; }
            else if( columnValue == "pendingapproval" ) { return CaseStatus.PendingApproval; }
            else if( columnValue == "customercontacted" ) { return CaseStatus.CustomerContacted; }
            else if( columnValue == "inprogress" ) { return CaseStatus.InProgress; }
            else if( columnValue == "pendingrefund" ) { return CaseStatus.FollowUp; }
            else if( columnValue == "pendingreturn" ) { return CaseStatus.FollowUp; }
            else if( columnValue == "ncl" ) { return CaseStatus.FollowUp; }
            else if( columnValue == "" ) { return CaseStatus.FollowUp; }
            else if( columnValue == "leftcontactinfo" ) { return CaseStatus.FollowUp; }
            else {
                throw new Exception("Status cannot be translated: " + originalValue);
            }
        }

        private WhereFrom getWhereFromFromString ( string columnValue ) {
            string originalValue = columnValue;
            columnValue = columnValue.ToLower().Replace(" ", "");
            if( columnValue == "" ) { return WhereFrom.Assigned; }
            else if( columnValue == "liveline" ) { return WhereFrom.LiveLine; }
            else {
                throw new Exception("WhereFrom cannot be translated: " + originalValue);
            }
        }

        private DateTime getDateTimeFromString ( string dateColumnValue, string timeColumnValue ) {
            DateTime date;
            DateTime time;
            try {
                date = DateTime.Parse(dateColumnValue);
            }
            catch {
                throw new Exception("Date cannot be translated: " + dateColumnValue);
            }
            try {
                time = DateTime.Parse(timeColumnValue);
            }
            catch {
                throw new Exception("Time cannot be translated: " + timeColumnValue);
            }

            return new DateTime(date.Year, date.Month, date.Day, time.Hour, time.Minute, time.Second);
        }

        private void tryToGetName ( CaseRow cr, string description ) {
            int indexOfDash = description.IndexOf('-');
            int indexOfSlash = description.IndexOf('/');
            string[] wordArray = new string[2];
            bool found = false;
            if( indexOfDash > 0 ) {
                string[] words = description.Substring(0, indexOfDash).Trim().Split(new char[] { ' ' });
                if( words.Length == 2 ) {
                    wordArray = words;
                    cr.Description = description.Remove(0, indexOfDash + 1).Trim(); //if ok, remove name
                    found = true;
                }
            }
            if( !found && indexOfSlash > 0 ) {
                string[] words = description.Substring(0, indexOfSlash).Trim().Split(new char[] { ' ' });
                if( words.Length == 2 ) {
                    wordArray = words;
                    cr.Description = description.Remove(0, indexOfSlash + 1).Trim(); //if ok, remove name
                    found = true;
                }
            }
            if( !found ) {
                string[] words = description.Trim().Split(new char[] { ' ' });
                if( words.Length == 2 ) {
                    wordArray = words;
                    found = true;
                }
            }
            if( found ) {
                cr.CustomerName = wordArray[0] + " " + wordArray[1];
            }
        }
        #endregion

        public List<CaseStatusPair> CaseStatusList {
            get { return caseStatusList; }
        }
        public List<WhereFromPair> WhereFromList {
            get { return whereFromList; }
        }
    }
}
